USE Competencias
/** PRUEBAS_LST **/
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'PRUEBAS_LST' AND type = 'P')
   DROP PROCEDURE PRUEBAS_LST
GO
USE Competencias
GO
CREATE PROCEDURE PRUEBAS_LST
@IDCATEGORIA INT
AS
SET NOCOUNT ON
SELECT C_NOMPRU, B_TIPPRU + '-' + CAST(N_CODPRU AS VARCHAR) AS 'TIPOCODIGO'
FROM TCPRUEBA
WHERE N_CATPRU = @IDCATEGORIA
/** BORRAR_SORTEO **/
GO
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'BORRAR_SORTEO' AND type = 'P')
   DROP PROCEDURE BORRAR_SORTEO
GO
USE Competencias
GO
CREATE PROCEDURE BORRAR_SORTEO
AS
SET NOCOUNT ON
DELETE FROM TCSORTEO
GO
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'SP_FINAL' AND type = 'P')
   DROP PROCEDURE SP_FINAL
/** SP_PASEFINAL **/
GO
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'SP_PASEFINAL' AND type = 'P')
   DROP PROCEDURE SP_PASEFINAL
GO
USE Competencias
GO
CREATE PROCEDURE [SP_PASEFINAL] ( @N_CODCAM INT ,@N_CODPRU INT, @C_MEDPRU CHAR, @OPCION INT)

AS
BEGIN
 DECLARE @ERROR int
 SELECT @ERROR = 0

	SET NOCOUNT ON;
	IF @OPCION=1
 	 BEGIN
		--Actualizar datos de alumnos
		UPDATE
    	TDPRUEBA_ALUMNO
    	SET
    	B_ESFIN='F'
    	WHERE N_CODCAM= @N_CODCAM AND N_CODPRU=@N_CODPRU 
		AND   B_ESFIN='S'
     END
    ELSE
	 BEGIN
	 --Filtrar alumnos a migrar
		IF @C_MEDPRU='1'
 			BEGIN
				SELECT TOP(8) N_CODPRU,N_CODCAM,N_CODALU,N_SERPRU,F_RESPRU,F_TIEPRU, N_PUNPRU,B_ESFIN 
				INTO #CambiaFinal
				FROM TDPRUEBA_ALUMNO
				WHERE N_CODCAM= @N_CODCAM 
				AND N_CODPRU=@N_CODPRU 
				AND B_ESFIN='S'
				ORDER BY F_TIEPRU ASC
			
			 --Actualizar datos en tabla temporal
				UPDATE
				#CambiaFinal
				SET
				B_ESFIN='F',
				F_RESPRU=NULL,
				F_TIEPRU=NULL    	
				--Insertar valores a tabla de pruebas
				INSERT INTO TDPRUEBA_ALUMNO (N_CODPRU,N_CODCAM,N_CODALU,N_SERPRU,F_RESPRU,F_TIEPRU, N_PUNPRU,B_ESFIN) 
				SELECT * FROM  #CambiaFinal			
			END
		ELSE
			BEGIN
				SELECT TOP(8) N_CODPRU,N_CODCAM,N_CODALU,N_SERPRU,F_RESPRU,F_TIEPRU, N_PUNPRU,B_ESFIN
				INTO #CambiaFinalAsc
				FROM TDPRUEBA_ALUMNO
				WHERE N_CODCAM= @N_CODCAM 
				AND N_CODPRU=@N_CODPRU 
				AND B_ESFIN='S'
				ORDER BY F_RESPRU DESC
			
			 --Actualizar datos en tabla temporal
				UPDATE
				#CambiaFinalAsc
				SET
				B_ESFIN='F',
				F_RESPRU=NULL,
				F_TIEPRU=NULL    	
				--Insertar valores a tabla de pruebas
				INSERT INTO TDPRUEBA_ALUMNO (N_CODPRU,N_CODCAM,N_CODALU,N_SERPRU,F_RESPRU,F_TIEPRU, N_PUNPRU,B_ESFIN) 
				SELECT * FROM  #CambiaFinalAsc			
				
			END
     END
SET NOCOUNT OFF  
/*	IF @error = 0
		SELECT @error = @@error
	IF @error = 0
		COMMIT TRANSACTION
	ELSE
		ROLLBACK TRANSACTION*/
 END
GO